
/****** Object:  StoredProcedure [proc_SalesLoadByPrimaryKey]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_SalesLoadByPrimaryKey]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_SalesLoadByPrimaryKey];
GO

CREATE PROCEDURE [proc_SalesLoadByPrimaryKey]
(
	@SaleID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[SaleID],
		[ProdID],
		[SaledPrice],
		[SaleDate],
		[SaleTime],
		[SaleQuantity]
	FROM [Sales]
	WHERE
		([SaleID] = @SaleID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_SalesLoadByPrimaryKey Succeeded'
ELSE PRINT 'Procedure Creation: proc_SalesLoadByPrimaryKey Error on Creation'
GO

/****** Object:  StoredProcedure [proc_SalesLoadAll]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_SalesLoadAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_SalesLoadAll];
GO

CREATE PROCEDURE [proc_SalesLoadAll]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[SaleID],
		[ProdID],
		[SaledPrice],
		[SaleDate],
		[SaleTime],
		[SaleQuantity]
	FROM [Sales]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_SalesLoadAll Succeeded'
ELSE PRINT 'Procedure Creation: proc_SalesLoadAll Error on Creation'
GO

/****** Object:  StoredProcedure [proc_SalesUpdate]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_SalesUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_SalesUpdate];
GO

CREATE PROCEDURE [proc_SalesUpdate]
(
	@SaleID int,
	@ProdID int = NULL,
	@SaledPrice float = NULL,
	@SaleDate nvarchar(50) = NULL,
	@SaleTime nvarchar(50) = NULL,
	@SaleQuantity int = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Sales]
	SET
		[ProdID] = @ProdID,
		[SaledPrice] = @SaledPrice,
		[SaleDate] = @SaleDate,
		[SaleTime] = @SaleTime,
		[SaleQuantity] = @SaleQuantity
	WHERE
		[SaleID] = @SaleID


	SET @Err = @@Error


	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_SalesUpdate Succeeded'
ELSE PRINT 'Procedure Creation: proc_SalesUpdate Error on Creation'
GO




/****** Object:  StoredProcedure [proc_SalesInsert]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_SalesInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_SalesInsert];
GO

CREATE PROCEDURE [proc_SalesInsert]
(
	@SaleID int = NULL output,
	@ProdID int = NULL,
	@SaledPrice float = NULL,
	@SaleDate nvarchar(50) = NULL,
	@SaleTime nvarchar(50) = NULL,
	@SaleQuantity int = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	INSERT
	INTO [Sales]
	(
		[ProdID],
		[SaledPrice],
		[SaleDate],
		[SaleTime],
		[SaleQuantity]
	)
	VALUES
	(
		@ProdID,
		@SaledPrice,
		@SaleDate,
		@SaleTime,
		@SaleQuantity
	)

	SET @Err = @@Error

	SELECT @SaleID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_SalesInsert Succeeded'
ELSE PRINT 'Procedure Creation: proc_SalesInsert Error on Creation'
GO

/****** Object:  StoredProcedure [proc_SalesDelete]    Script Date: 21/3/2015 6:45:12 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_SalesDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_SalesDelete];
GO

CREATE PROCEDURE [proc_SalesDelete]
(
	@SaleID int
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	DELETE
	FROM [Sales]
	WHERE
		[SaleID] = @SaleID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: proc_SalesDelete Succeeded'
ELSE PRINT 'Procedure Creation: proc_SalesDelete Error on Creation'
GO
